package DBTasks;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 
 * @author Stephen
 *
 */

public class CurrentYearPetAdoptions extends DBTask {
	
	private static String createDogsInShelterViewSQL = 
			"CREATE OR REPLACE View DogsInShelter AS SELECT * FROM Dog WHERE Shelter_Name = ?;";
	
	private static String createCatsInShelterViewSQL = 	
			"CREATE OR REPLACE View CatsInShelter AS SELECT * FROM Cat WHERE Shelter_Name = ?";
	
	private static String currentYearPetAdoptionsSQL =
			"SELECT * FROM CurrentYearPetAdoptions WHERE MONTH <= MONTH(NOW())";
	
	public static List<AdoptionsSummary> getReport(String shelterName){
		List<AdoptionsSummary> summaries = new ArrayList<AdoptionsSummary>();
		
		try {
			
			PreparedStatement createViews = con.prepareStatement(createDogsInShelterViewSQL);
			createViews.setString(1, shelterName);
			createViews.execute();
			
			createViews = con.prepareStatement(createCatsInShelterViewSQL);
			createViews.setString(1, shelterName);
			createViews.execute();
			
			ResultSet rs = createViews.executeQuery(currentYearPetAdoptionsSQL);
			
			while (rs.next()){
				AdoptionsSummary as = new AdoptionsSummary();
				
				as.setAdoptedDogs(rs.getInt("AD"));
				as.setNewDogs(rs.getInt("ND"));
				as.setShelteredDogs(rs.getInt("SD"));
				as.setFosteredDogs(rs.getInt("FD"));
				
				as.setAdoptedCats(rs.getInt("AC"));
				as.setNewCats(rs.getInt("NC"));
				as.setShelteredCats(rs.getInt("SC"));
				as.setFosteredCats(rs.getInt("FC"));
				
				summaries.add(as);
			}
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			summaries = null;
		}
		
		return summaries;
	}

}
